<!DOCTYPE html>
<html lang="zh-CN" data-theme="light">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width,initial-scale=1" />
    <meta name="generator" content="VuePress 2.0.0-beta.38" />
    <meta name="theme" content="VuePress Theme Hope" />
    <meta property="og:url" content="https://javaguide.cn/database/mysql/index-invalidation-caused-by-implicit-conversion.html"><meta property="og:site_name" content="JavaGuide"><meta property="og:title" content="MySQL中的隐式转换造成的索引失效"><meta property="og:type" content="article"><meta property="og:updated_time" content="2022-04-06T01:52:22.000Z"><meta property="og:locale" content="zh-CN"><meta property="article:tag" content="MySQL"><meta property="article:tag" content="性能优化"><meta property="article:modified_time" content="2022-04-06T01:52:22.000Z"><script>var _hmt = _hmt || [];
      (function() {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?5dd2e8c97962d57b7b8fea1737c01743";
        var s = document.getElementsByTagName("script")[0]; 
        s.parentNode.insertBefore(hm, s);
      })();</script><link rel="stylesheet" href="//at.alicdn.com/t/font_2922463_99aa80ii7cf.css"><title>MySQL中的隐式转换造成的索引失效 | JavaGuide</title><meta name="description" content="Java学习&&面试指南">
    <style>
      :root {
        --bg-color: #fff;
      }

      html[data-theme="dark"] {
        --bg-color: #1d2025;
      }

      html,
      body {
        background-color: var(--bg-color);
      }
    </style>
    <script>
      const userMode = localStorage.getItem("vuepress-theme-hope-scheme");
      const systemDarkMode =
        window.matchMedia &&
        window.matchMedia("(prefers-color-scheme: dark)").matches;

      if (userMode === "dark" || (userMode !== "light" && systemDarkMode)) {
        document.querySelector("html").setAttribute("data-theme", "dark");
      }
    </script>
    <link rel="stylesheet" href="/assets/style.aa943f56.css">
    <link rel="modulepreload" href="/assets/app.93341f6d.js"><link rel="modulepreload" href="/assets/index-invalidation-caused-by-implicit-conversion.html.45117209.js"><link rel="modulepreload" href="/assets/index-invalidation-caused-by-implicit-conversion.html.b6369464.js"><link rel="modulepreload" href="/assets/plugin-vue_export-helper.21dcd24c.js">
  </head>
  <body>
    <div id="app"><!--[--><!--[--><!--[--><span tabindex="-1"></span><a href="#main-content" class="skip-link sr-only">Skip to content</a><!--]--><div class="theme-container has-toc sidebar-open"><!--[--><header class="navbar"><button class="toggle-sidebar-button" title="Toggle Sidebar"><span class="icon"></span></button><a href="/" class="home-link"><img class="logo" src="/logo.png" alt="JavaGuide"><!----><span class="site-name hide-in-pad">JavaGuide</span><!--[--><!----><!--]--></a><nav class="nav-links" style=""><div class="nav-item hide-in-mobile"><a href="/home.html" class="nav-link" arialabel="面试指南"><i class="icon iconfont icon-java"></i>面试指南<!----></a></div><div class="nav-item hide-in-mobile"><a href="/zhuanlan/" class="nav-link" arialabel="优质专栏"><i class="icon iconfont icon-recommend"></i>优质专栏<!----></a></div><div class="nav-item hide-in-mobile"><a href="/open-source-project/" class="nav-link" arialabel="项目精选"><i class="icon iconfont icon-github"></i>项目精选<!----></a></div><div class="nav-item hide-in-mobile"><a href="/books/" class="nav-link" arialabel="书籍精选"><i class="icon iconfont icon-book"></i>书籍精选<!----></a></div><div class="nav-item hide-in-mobile"><a href="https://snailclimb.gitee.io/javaguide/#/" rel="noopener noreferrer" target="_blank" arialabel="旧版链接" class="nav-link"><i class="icon iconfont icon-java"></i>旧版链接<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span><!----></a></div><div class="nav-item hide-in-mobile"><a href="https://javaguide.cn/feed.json" rel="noopener noreferrer" target="_blank" arialabel="RSS订阅" class="nav-link"><i class="icon iconfont icon-rss"></i>RSS订阅<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span><!----></a></div><div class="nav-item hide-in-mobile"><a href="/about-the-author/" class="nav-link" arialabel="关于作者"><i class="icon iconfont icon-zuozhe"></i>关于作者<!----></a></div></nav><div class="nav-actions-wrapper"><!--[--><!----><!--]--><div class="nav-item"><!----></div><div class="nav-item"><a class="repo-link" href="https://github.com/Snailclimb/JavaGuide" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" class="icon github-icon" viewbox="0 0 1024 1024" arialabelledby="github" style="width:1.25rem;height:1.25rem;vertical-align:middle;"><title id="github" lang="en">github icon</title><g fill="currentColor"><path d="M511.957 21.333C241.024 21.333 21.333 240.981 21.333 512c0 216.832 140.544 400.725 335.574 465.664 24.49 4.395 32.256-10.07 32.256-23.083 0-11.69.256-44.245 0-85.205-136.448 29.61-164.736-64.64-164.736-64.64-22.315-56.704-54.4-71.765-54.4-71.765-44.587-30.464 3.285-29.824 3.285-29.824 49.195 3.413 75.179 50.517 75.179 50.517 43.776 75.008 114.816 53.333 142.762 40.79 4.523-31.66 17.152-53.377 31.19-65.537-108.971-12.458-223.488-54.485-223.488-242.602 0-53.547 19.114-97.323 50.517-131.67-5.035-12.33-21.93-62.293 4.779-129.834 0 0 41.258-13.184 134.912 50.346a469.803 469.803 0 0 1 122.88-16.554c41.642.213 83.626 5.632 122.88 16.554 93.653-63.488 134.784-50.346 134.784-50.346 26.752 67.541 9.898 117.504 4.864 129.834 31.402 34.347 50.474 78.123 50.474 131.67 0 188.586-114.73 230.016-224.042 242.09 17.578 15.232 33.578 44.672 33.578 90.454v135.85c0 13.142 7.936 27.606 32.854 22.87C862.25 912.597 1002.667 728.747 1002.667 512c0-271.019-219.648-490.667-490.71-490.667z"></path></g></svg></a></div><div class="nav-item hide-in-mobile"><button id="appearance-switch"><svg xmlns="http://www.w3.org/2000/svg" class="icon auto-icon" viewbox="0 0 1024 1024" arialabelledby="auto" style="display:block;"><title id="auto" lang="en">auto icon</title><g fill="currentColor"><path d="M512 992C246.92 992 32 777.08 32 512S246.92 32 512 32s480 214.92 480 480-214.92 480-480 480zm0-840c-198.78 0-360 161.22-360 360 0 198.84 161.22 360 360 360s360-161.16 360-360c0-198.78-161.22-360-360-360zm0 660V212c165.72 0 300 134.34 300 300 0 165.72-134.28 300-300 300z"></path></g></svg><svg xmlns="http://www.w3.org/2000/svg" class="icon dark-icon" viewbox="0 0 1024 1024" arialabelledby="dark" style="display:none;"><title id="dark" lang="en">dark icon</title><g fill="currentColor"><path d="M524.8 938.667h-4.267a439.893 439.893 0 0 1-313.173-134.4 446.293 446.293 0 0 1-11.093-597.334A432.213 432.213 0 0 1 366.933 90.027a42.667 42.667 0 0 1 45.227 9.386 42.667 42.667 0 0 1 10.24 42.667 358.4 358.4 0 0 0 82.773 375.893 361.387 361.387 0 0 0 376.747 82.774 42.667 42.667 0 0 1 54.187 55.04 433.493 433.493 0 0 1-99.84 154.88 438.613 438.613 0 0 1-311.467 128z"></path></g></svg><svg xmlns="http://www.w3.org/2000/svg" class="icon light-icon" viewbox="0 0 1024 1024" arialabelledby="light" style="display:none;"><title id="light" lang="en">light icon</title><g fill="currentColor"><path d="M952 552h-80a40 40 0 0 1 0-80h80a40 40 0 0 1 0 80zM801.88 280.08a41 41 0 0 1-57.96-57.96l57.96-58a41.04 41.04 0 0 1 58 58l-58 57.96zM512 752a240 240 0 1 1 0-480 240 240 0 0 1 0 480zm0-560a40 40 0 0 1-40-40V72a40 40 0 0 1 80 0v80a40 40 0 0 1-40 40zm-289.88 88.08-58-57.96a41.04 41.04 0 0 1 58-58l57.96 58a41 41 0 0 1-57.96 57.96zM192 512a40 40 0 0 1-40 40H72a40 40 0 0 1 0-80h80a40 40 0 0 1 40 40zm30.12 231.92a41 41 0 0 1 57.96 57.96l-57.96 58a41.04 41.04 0 0 1-58-58l58-57.96zM512 832a40 40 0 0 1 40 40v80a40 40 0 0 1-80 0v-80a40 40 0 0 1 40-40zm289.88-88.08 58 57.96a41.04 41.04 0 0 1-58 58l-57.96-58a41 41 0 0 1 57.96-57.96z"></path></g></svg></button></div><form class="search-box" role="search"><input type="search" placeholder="搜索" autocomplete="off" spellcheck="false" value><!----></form><button class="toggle-navbar-button" aria-label="Toggle Navbar" aria-expanded="false" aria-controls="nav-screen"><span class="button-container"><span class="button-top"></span><span class="button-middle"></span><span class="button-bottom"></span></span></button><!--[--><!----><!--]--></div></header><!----><!--]--><!----><div class="toggle-sidebar-wrapper"><span class="arrow left"></span></div><aside class="sidebar"><!--[--><!----><!--]--><ul class="sidebar-links"><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-mianshi"></i><span class="title">面试准备</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-java"></i><span class="title">Java</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-computer"></i><span class="title">计算机基础</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable active"><i class="icon iconfont icon-database"></i><span class="title">数据库</span><span class="arrow down"></span></button><ul class="sidebar-links"><li><!--[--><a href="/database/basis.html" class="nav-link sidebar-link sidebar-page" arialabel="数据库基础知识"><!---->数据库基础知识<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/character-set.html" class="nav-link sidebar-link sidebar-page" arialabel="字符集"><!---->字符集<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable active"><i class="icon iconfont icon-mysql"></i><span class="title">MySQL</span><span class="arrow down"></span></button><ul class="sidebar-links"><li><!--[--><a href="/database/mysql/mysql-questions-01.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL知识点&amp;面试题总结"><!---->MySQL知识点&amp;面试题总结<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/a-thousand-lines-of-mysql-study-notes.html" class="nav-link sidebar-link sidebar-page" arialabel="一千行 MySQL 学习笔记"><!---->一千行 MySQL 学习笔记<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/mysql-high-performance-optimization-specification-recommendations.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL高性能优化规范建议"><!---->MySQL高性能优化规范建议<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable active"><i class="icon iconfont icon-important"></i><span class="title">重要知识点</span><span class="arrow down"></span></button><ul class="sidebar-links"><li><!--[--><a href="/database/mysql/mysql-index.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL索引详解"><!---->MySQL索引详解<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/mysql-logs.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL三大日志(binlog、redo log和undo log)详解"><!---->MySQL三大日志(binlog、redo log和undo log)详解<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/transaction-isolation-level.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL事务隔离级别详解"><!---->MySQL事务隔离级别详解<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/innodb-implementation-of-mvcc.html" class="nav-link sidebar-link sidebar-page" arialabel="InnoDB存储引擎对MVCC的实现"><!---->InnoDB存储引擎对MVCC的实现<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/how-sql-executed-in-mysql.html" class="nav-link sidebar-link sidebar-page" arialabel="SQL语句在MySQL中的执行过程"><!---->SQL语句在MySQL中的执行过程<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/some-thoughts-on-database-storage-time.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL数据库时间类型数据存储建议"><!---->MySQL数据库时间类型数据存储建议<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a aria-current="page" href="/database/mysql/index-invalidation-caused-by-implicit-conversion.html" class="router-link-active router-link-exact-active nav-link active sidebar-link sidebar-page active" arialabel="MySQL中的隐式转换造成的索引失效"><!---->MySQL中的隐式转换造成的索引失效<!----></a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/index-invalidation-caused-by-implicit-conversion.html#前言" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="前言"><!---->前言<!----></a><ul class="sidebar-sub-headers"></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/index-invalidation-caused-by-implicit-conversion.html#数据准备" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="数据准备"><!---->数据准备<!----></a><ul class="sidebar-sub-headers"></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/index-invalidation-caused-by-implicit-conversion.html#sql-测试" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="SQL 测试"><!---->SQL 测试<!----></a><ul class="sidebar-sub-headers"></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/index-invalidation-caused-by-implicit-conversion.html#分析和总结" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="分析和总结"><!---->分析和总结<!----></a><ul class="sidebar-sub-headers"></ul></li></ul><!--]--></li></ul></section><!--]--></li></ul></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-redis"></i><span class="title">Redis</span><span class="arrow right"></span></button><!----></section><!--]--></li></ul></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-Tools"></i><span class="title">开发工具</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-xitongsheji"></i><span class="title">系统设计</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-distributed-network"></i><span class="title">分布式</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-et-performance"></i><span class="title">高性能</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-CalendarAvailability-1"></i><span class="title">高可用</span><span class="arrow right"></span></button><!----></section><!--]--></li></ul><!--[--><!----><!--]--></aside><!--[--><main class="page" id="main-content"><!----><nav class="breadcrumb disable"></nav><div class="page-title"><h1><!---->MySQL中的隐式转换造成的索引失效</h1><div class="article-info"><span class="author-info" arialabel="作者🖊" isoriginal="false" pageview="false" color="false"><svg xmlns="http://www.w3.org/2000/svg" class="icon author-icon" viewbox="0 0 1024 1024" arialabelledby="author"><title id="author" lang="en">author icon</title><g fill="currentColor"><path d="M649.6 633.6c86.4-48 147.2-144 147.2-249.6 0-160-128-288-288-288s-288 128-288 288c0 108.8 57.6 201.6 147.2 249.6-121.6 48-214.4 153.6-240 288-3.2 9.6 0 19.2 6.4 25.6 3.2 9.6 12.8 12.8 22.4 12.8h704c9.6 0 19.2-3.2 25.6-12.8 6.4-6.4 9.6-16 6.4-25.6-25.6-134.4-121.6-240-243.2-288z"></path></g></svg><span><a class="author-item" href="https://javaguide.cn/" target="_blank" rel="noopener noreferrer">Guide</a></span><span property="author" content="Guide"></span></span><span class="category-info" arialabel="分类🌈" isoriginal="false" pageview="false"><svg xmlns="http://www.w3.org/2000/svg" class="icon category-icon" viewbox="0 0 1024 1024" arialabelledby="category"><title id="category" lang="en">category icon</title><g fill="currentColor"><path d="M148.41 106.992h282.176c22.263 0 40.31 18.048 40.31 40.31V429.48c0 22.263-18.047 40.31-40.31 40.31H148.41c-22.263 0-40.311-18.047-40.311-40.31V147.302c0-22.263 18.048-40.31 40.311-40.31zM147.556 553.478H429.73c22.263 0 40.311 18.048 40.311 40.31v282.176c0 22.263-18.048 40.312-40.31 40.312H147.555c-22.263 0-40.311-18.049-40.311-40.312V593.79c0-22.263 18.048-40.311 40.31-40.311zM593.927 106.992h282.176c22.263 0 40.31 18.048 40.31 40.31V429.48c0 22.263-18.047 40.31-40.31 40.31H593.927c-22.263 0-40.311-18.047-40.311-40.31V147.302c0-22.263 18.048-40.31 40.31-40.31zM730.22 920.502H623.926c-40.925 0-74.22-33.388-74.22-74.425V623.992c0-41.038 33.387-74.424 74.425-74.424h222.085c41.038 0 74.424 33.226 74.424 74.067v114.233c0 10.244-8.304 18.548-18.547 18.548s-18.548-8.304-18.548-18.548V623.635c0-20.388-16.746-36.974-37.33-36.974H624.13c-20.585 0-37.331 16.747-37.331 37.33v222.086c0 20.585 16.654 37.331 37.126 37.331H730.22c10.243 0 18.547 8.304 18.547 18.547 0 10.244-8.304 18.547-18.547 18.547z"></path></g></svg><ul class="categories-wrapper"><li class="category clickable" role="navigation">数据库</li><meta property="articleSection" content="数据库"></ul></span><span arialabel="标签🏷" isoriginal="false" pageview="false"><svg xmlns="http://www.w3.org/2000/svg" class="icon tag-icon" viewbox="0 0 1024 1024" arialabelledby="tag"><title id="tag" lang="en">tag icon</title><g fill="currentColor"><path d="M939.902 458.563L910.17 144.567c-1.507-16.272-14.465-29.13-30.737-30.737L565.438 84.098h-.402c-3.215 0-5.726 1.005-7.634 2.913l-470.39 470.39a10.004 10.004 0 000 14.164l365.423 365.424c1.909 1.908 4.42 2.913 7.132 2.913s5.223-1.005 7.132-2.913l470.39-470.39c2.01-2.11 3.014-5.023 2.813-8.036zm-240.067-72.121c-35.458 0-64.286-28.828-64.286-64.286s28.828-64.285 64.286-64.285 64.286 28.828 64.286 64.285-28.829 64.286-64.286 64.286z"></path></g></svg><ul class="tags-wrapper"><li class="tag clickable" role="navigation">MySQL</li><li class="tag clickable" role="navigation">性能优化</li></ul><meta property="keywords" content="MySQL,性能优化"></span><span class="date-info" arialabel="写作日期📅" isoriginal="false" pageview="false" color="false"><svg xmlns="http://www.w3.org/2000/svg" class="icon calendar-icon" viewbox="0 0 1024 1024" arialabelledby="calendar"><title id="calendar" lang="en">calendar icon</title><g fill="currentColor"><path d="M716.4 110.137c0-18.753-14.72-33.473-33.472-33.473-18.753 0-33.473 14.72-33.473 33.473v33.473h66.993v-33.473zm-334.87 0c0-18.753-14.72-33.473-33.473-33.473s-33.52 14.72-33.52 33.473v33.473h66.993v-33.473zm468.81 33.52H716.4v100.465c0 18.753-14.72 33.473-33.472 33.473a33.145 33.145 0 01-33.473-33.473V143.657H381.53v100.465c0 18.753-14.72 33.473-33.473 33.473a33.145 33.145 0 01-33.473-33.473V143.657H180.6A134.314 134.314 0 0046.66 277.595v535.756A134.314 134.314 0 00180.6 947.289h669.74a134.36 134.36 0 00133.94-133.938V277.595a134.314 134.314 0 00-133.94-133.938zm33.473 267.877H147.126a33.145 33.145 0 01-33.473-33.473c0-18.752 14.72-33.473 33.473-33.473h736.687c18.752 0 33.472 14.72 33.472 33.473a33.145 33.145 0 01-33.472 33.473z"></path></g></svg><span>2022年4月6日</span><meta property="datePublished" content="2022-04-06T01:52:22.000Z"></span><!----><span class="words-info" arialabel="字数🔠" isoriginal="false" pageview="false" color="false"><svg xmlns="http://www.w3.org/2000/svg" class="icon word-icon" viewbox="0 0 1024 1024" arialabelledby="word"><title id="word" lang="en">word icon</title><g fill="currentColor"><path d="M518.217 432.64V73.143A73.143 73.143 0 01603.43 1.097a512 512 0 01419.474 419.474 73.143 73.143 0 01-72.046 85.212H591.36a73.143 73.143 0 01-73.143-73.143z"></path><path d="M493.714 566.857h340.297a73.143 73.143 0 0173.143 85.577A457.143 457.143 0 11371.566 117.76a73.143 73.143 0 0185.577 73.143v339.383a36.571 36.571 0 0036.571 36.571z"></path></g></svg><span>约 2688 字</span><meta property="wordCount" content="2688"></span></div><hr></div><div class="toc-place-holder"><aside id="toc-list"><div class="toc-header">此页内容</div><div class="toc-wrapper"><ul class="toc-list"><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/index-invalidation-caused-by-implicit-conversion.html#前言" class="router-link-active router-link-exact-active toc-link level2">前言</a></li><!----><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/index-invalidation-caused-by-implicit-conversion.html#数据准备" class="router-link-active router-link-exact-active toc-link level2">数据准备</a></li><!----><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/index-invalidation-caused-by-implicit-conversion.html#sql-测试" class="router-link-active router-link-exact-active toc-link level2">SQL 测试</a></li><!----><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/index-invalidation-caused-by-implicit-conversion.html#分析和总结" class="router-link-active router-link-exact-active toc-link level2">分析和总结</a></li><!----><!--]--></ul></div></aside></div><!----><div class="theme-hope-content"><!--[--><blockquote><p>本次测试使用的 MySQL 版本是 <code>5.7.26</code>，随着 MySQL 版本的更新某些特性可能会发生改变，本文不代表所述观点和结论于 MySQL 所有版本均准确无误，版本差异请自行甄别。</p><p>原文：https://www.guitu18.com/post/2019/11/24/61.html</p></blockquote><h2 id="前言" tabindex="-1"><a class="header-anchor" href="#前言" aria-hidden="true">#</a> 前言</h2><p>数据库优化是一个任重而道远的任务，想要做优化必须深入理解数据库的各种特性。在开发过程中我们经常会遇到一些原因很简单但造成的后果却很严重的疑难杂症，这类问题往往还不容易定位，排查费时费力最后发现是一个很小的疏忽造成的，又或者是因为不了解某个技术特性产生的。</p><p>于数据库层面，最常见的恐怕就是索引失效了，且一开始因为数据量小还不易被发现。但随着业务的拓展数据量的提升，性能问题慢慢的就体现出来了，处理不及时还很容易造成雪球效应，最终导致数据库卡死甚至瘫痪。造成索引失效的原因可能有很多种，相关技术博客已经有太多了，今天我要记录的是<strong>隐式转换造成的索引失效</strong>。</p><h2 id="数据准备" tabindex="-1"><a class="header-anchor" href="#数据准备" aria-hidden="true">#</a> 数据准备</h2><p>首先使用存储过程生成 1000 万条测试数据， 测试表一共建立了 7 个字段（包括主键），<code>num1</code>和<code>num2</code>保存的是和<code>ID</code>一样的顺序数字，其中<code>num2</code>是字符串类型。 <code>type1</code>和<code>type2</code>保存的都是主键对 5 的取模，目的是模拟实际应用中常用类似 type 类型的数据，但是<code>type2</code>是没有建立索引的。 <code>str1</code>和<code>str2</code>都是保存了一个 20 位长度的随机字符串，<code>str1</code>不能为<code>NULL</code>，<code>str2</code>允许为<code>NULL</code>，相应的生成测试数据的时候我也会在<code>str2</code>字段生产少量<code>NULL</code>值（每 100 条数据产生一个<code>NULL</code>值）。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 创建测试数据表</span>
<span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> test1<span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token identifier"><span class="token punctuation">`</span>test1<span class="token punctuation">`</span></span> <span class="token punctuation">(</span>
    <span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>num1<span class="token punctuation">`</span></span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">&#39;0&#39;</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>num2<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">&#39;&#39;</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>type1<span class="token punctuation">`</span></span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">&#39;0&#39;</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>type2<span class="token punctuation">`</span></span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">&#39;0&#39;</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>str1<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">&#39;&#39;</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>str2<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">KEY</span> <span class="token identifier"><span class="token punctuation">`</span>num1<span class="token punctuation">`</span></span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>num1<span class="token punctuation">`</span></span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">KEY</span> <span class="token identifier"><span class="token punctuation">`</span>num2<span class="token punctuation">`</span></span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>num2<span class="token punctuation">`</span></span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">KEY</span> <span class="token identifier"><span class="token punctuation">`</span>type1<span class="token punctuation">`</span></span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>type1<span class="token punctuation">`</span></span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">KEY</span> <span class="token identifier"><span class="token punctuation">`</span>str1<span class="token punctuation">`</span></span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>str1<span class="token punctuation">`</span></span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">KEY</span> <span class="token identifier"><span class="token punctuation">`</span>str2<span class="token punctuation">`</span></span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>str2<span class="token punctuation">`</span></span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8<span class="token punctuation">;</span>
<span class="token comment">-- 创建存储过程</span>
<span class="token keyword">DROP</span> <span class="token keyword">PROCEDURE</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> pre_test1<span class="token punctuation">;</span>
<span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> <span class="token identifier"><span class="token punctuation">`</span>pre_test1<span class="token punctuation">`</span></span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> i <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
    <span class="token keyword">SET</span> autocommit <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>
    <span class="token keyword">WHILE</span> i <span class="token operator">&lt;</span> <span class="token number">10000000</span> <span class="token keyword">DO</span>
        <span class="token keyword">SET</span> i <span class="token operator">=</span> i <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
        <span class="token keyword">SET</span> <span class="token variable">@str1</span> <span class="token operator">=</span> SUBSTRING<span class="token punctuation">(</span>MD5<span class="token punctuation">(</span>RAND<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">-- 每100条数据str2产生一个null值</span>
        <span class="token keyword">IF</span> i <span class="token operator">%</span> <span class="token number">100</span> <span class="token operator">=</span> <span class="token number">0</span> <span class="token keyword">THEN</span>
            <span class="token keyword">SET</span> <span class="token variable">@str2</span> <span class="token operator">=</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>
        <span class="token keyword">ELSE</span>
            <span class="token keyword">SET</span> <span class="token variable">@str2</span> <span class="token operator">=</span> <span class="token variable">@str1</span><span class="token punctuation">;</span>
        <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
        <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test1 <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>num1<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>num2<span class="token punctuation">`</span></span><span class="token punctuation">,</span>
        <span class="token identifier"><span class="token punctuation">`</span>type1<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>type2<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>str1<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>str2<span class="token punctuation">`</span></span><span class="token punctuation">)</span>
        <span class="token keyword">VALUES</span> <span class="token punctuation">(</span>CONCAT<span class="token punctuation">(</span><span class="token string">&#39;&#39;</span><span class="token punctuation">,</span> i<span class="token punctuation">)</span><span class="token punctuation">,</span> CONCAT<span class="token punctuation">(</span><span class="token string">&#39;&#39;</span><span class="token punctuation">,</span> i<span class="token punctuation">)</span><span class="token punctuation">,</span>
        CONCAT<span class="token punctuation">(</span><span class="token string">&#39;&#39;</span><span class="token punctuation">,</span> i<span class="token punctuation">)</span><span class="token punctuation">,</span> i<span class="token operator">%</span><span class="token number">5</span><span class="token punctuation">,</span> i<span class="token operator">%</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token variable">@str1</span><span class="token punctuation">,</span> <span class="token variable">@str2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">-- 事务优化，每一万条数据提交一次事务</span>
        <span class="token keyword">IF</span> i <span class="token operator">%</span> <span class="token number">10000</span> <span class="token operator">=</span> <span class="token number">0</span> <span class="token keyword">THEN</span>
            <span class="token keyword">COMMIT</span><span class="token punctuation">;</span>
        <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">WHILE</span><span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span>
<span class="token comment">// DELIMITER ;</span>
<span class="token comment">-- 执行存储过程</span>
<span class="token keyword">CALL</span> pre_test1<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br></div></div><p>数据量比较大，还涉及使用<code>MD5</code>生成随机字符串，所以速度有点慢，稍安勿躁，耐心等待即可。</p><p>1000 万条数据，我用了 33 分钟才跑完（实际时间跟你电脑硬件配置有关）。这里贴几条生成的数据，大致长这样。</p><p><img src="https://guide-blog-images.oss-cn-shenzhen.aliyuncs.com/github/javaguide/mysqlindex-invalidation-caused-by-implicit-conversion-01.png" alt="" loading="lazy"></p><h2 id="sql-测试" tabindex="-1"><a class="header-anchor" href="#sql-测试" aria-hidden="true">#</a> SQL 测试</h2><p>先来看这组 SQL，一共四条，我们的测试数据表<code>num1</code>是<code>int</code>类型，<code>num2</code>是<code>varchar</code>类型，但是存储的数据都是跟主键<code>id</code>一样的顺序数字，两个字段都建立有索引。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token number">1</span>: <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>test1<span class="token punctuation">`</span></span> <span class="token keyword">WHERE</span> num1 <span class="token operator">=</span> <span class="token number">10000</span><span class="token punctuation">;</span>
<span class="token number">2</span>: <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>test1<span class="token punctuation">`</span></span> <span class="token keyword">WHERE</span> num1 <span class="token operator">=</span> <span class="token string">&#39;10000&#39;</span><span class="token punctuation">;</span>
<span class="token number">3</span>: <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>test1<span class="token punctuation">`</span></span> <span class="token keyword">WHERE</span> num2 <span class="token operator">=</span> <span class="token number">10000</span><span class="token punctuation">;</span>
<span class="token number">4</span>: <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>test1<span class="token punctuation">`</span></span> <span class="token keyword">WHERE</span> num2 <span class="token operator">=</span> <span class="token string">&#39;10000&#39;</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>这四条 SQL 都是有针对性写的，12 查询的字段是 int 类型，34 查询的字段是<code>varchar</code>类型。12 或 34 查询的字段虽然都相同，但是一个条件是数字，一个条件是用引号引起来的字符串。这样做有什么区别呢？先不看下边的测试结果你能猜出这四条 SQL 的效率顺序吗？</p><p>经测试这四条 SQL 最后的执行结果却相差很大，其中 124 三条 SQL 基本都是瞬间出结果，大概在 0.001~0.005 秒，在千万级的数据量下这样的结果可以判定这三条 SQL 性能基本没差别了。但是第三条 SQL，多次测试耗时基本在 4.5~4.8 秒之间。</p><p>为什么 34 两条 SQL 效率相差那么大，但是同样做对比的 12 两条 SQL 却没什么差别呢？查看一下执行计划，下边分别 1234 条 SQL 的执行计划数据：</p><p><img src="https://guide-blog-images.oss-cn-shenzhen.aliyuncs.com/github/javaguide/mysqlindex-invalidation-caused-by-implicit-conversion-02.png" alt="" loading="lazy"></p><p>可以看到，124 三条 SQL 都能使用到索引，连接类型都为<code>ref</code>，扫描行数都为 1，所以效率非常高。再看看第三条 SQL，没有用上索引，所以为全表扫描，<code>rows</code>直接到达 1000 万了，所以性能差别才那么大。</p><p>仔细观察你会发现，34 两条 SQL 查询的字段<code>num2</code>是<code>varchar</code>类型的，查询条件等号右边加引号的第 4 条 SQL 是用到索引的，那么是查询的数据类型和字段数据类型不一致造成的吗？如果是这样那 12 两条 SQL 查询的字段<code>num1</code>是<code>int</code>类型，但是第 2 条 SQL 查询条件右边加了引号为什么还能用上索引呢。</p><p>查阅 MySQL 相关文档发现是隐式转换造成的，看一下官方的描述：</p><blockquote><p>官方文档： <a href="https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html?spm=5176.100239.blogcont47339.5.1FTben" target="_blank" rel="noopener noreferrer">12.2 Type Conversion in Expression Evaluation<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></p><p>当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。某些转换是隐式发生的。例如，MySQL 会根据需要自动将字符串转换为数字，反之亦然。以下规则描述了比较操作的转换方式：</p><ol><li>两个参数至少有一个是<code>NULL</code>时，比较的结果也是<code>NULL</code>，特殊的情况是使用<code>&lt;=&gt;</code>对两个<code>NULL</code>做比较时会返回<code>1</code>，这两种情况都不需要做类型转换</li><li>两个参数都是字符串，会按照字符串来比较，不做类型转换</li><li>两个参数都是整数，按照整数来比较，不做类型转换</li><li>十六进制的值和非数字做比较时，会被当做二进制串</li><li>有一个参数是<code>TIMESTAMP</code>或<code>DATETIME</code>，并且另外一个参数是常量，常量会被转换为<code>timestamp</code></li><li>有一个参数是<code>decimal</code>类型，如果另外一个参数是<code>decimal</code>或者整数，会将整数转换为<code>decimal</code>后进行比较，如果另外一个参数是浮点数，则会把<code>decimal</code>转换为浮点数进行比较</li><li><strong>所有其他情况下，两个参数都会被转换为浮点数再进行比较</strong></li></ol></blockquote><p>根据官方文档的描述，我们的第 23 两条 SQL 都发生了隐式转换，第 2 条 SQL 的查询条件<code>num1 = &#39;10000&#39;</code>，左边是<code>int</code>类型右边是字符串，第 3 条 SQL 相反，那么根据官方转换规则第 7 条，左右两边都会转换为浮点数再进行比较。</p><p>先看第 2 条 SQL：<code>SELECT * FROM</code>test1<code>WHERE num1 = &#39;10000&#39;;</code> <strong>左边为 int 类型</strong><code>10000</code>，转换为浮点数还是<code>10000</code>，右边字符串类型<code>&#39;10000&#39;</code>，转换为浮点数也是<code>10000</code>。两边的转换结果都是唯一确定的，所以不影响使用索引。</p><p>第 3 条 SQL：<code>SELECT * FROM</code>test1<code>WHERE num2 = 10000;</code> <strong>左边是字符串类型</strong><code>&#39;10000&#39;</code>，转浮点数为 10000 是唯一的，右边<code>int</code>类型<code>10000</code>转换结果也是唯一的。但是，因为左边是检索条件，<code>&#39;10000&#39;</code>转到<code>10000</code>虽然是唯一，但是其他字符串也可以转换为<code>10000</code>，比如<code>&#39;10000a&#39;</code>，<code>&#39;010000&#39;</code>，<code>&#39;10000&#39;</code>等等都能转为浮点数<code>10000</code>，这样的情况下，是不能用到索引的。</p><p>关于这个<strong>隐式转换</strong>我们可以通过查询测试验证一下，先插入几条数据，其中<code>num2=&#39;10000a&#39;</code>、<code>&#39;010000&#39;</code>和<code>&#39;10000&#39;</code>：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>test1<span class="token punctuation">`</span></span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>num1<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>num2<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>type1<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>type2<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>str1<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>str2<span class="token punctuation">`</span></span><span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">&#39;10000001&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;10000&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;10000a&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;0&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;0&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;2df3d9465ty2e4hd523&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;2df3d9465ty2e4hd523&#39;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>test1<span class="token punctuation">`</span></span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>num1<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>num2<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>type1<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>type2<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>str1<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>str2<span class="token punctuation">`</span></span><span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">&#39;10000002&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;10000&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;010000&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;0&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;0&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;2df3d9465ty2e4hd523&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;2df3d9465ty2e4hd523&#39;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>test1<span class="token punctuation">`</span></span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>num1<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>num2<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>type1<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>type2<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>str1<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>str2<span class="token punctuation">`</span></span><span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">&#39;10000003&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;10000&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39; 10000&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;0&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;0&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;2df3d9465ty2e4hd523&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;2df3d9465ty2e4hd523&#39;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>然后使用第三条 SQL 语句<code>SELECT * FROM</code>test1<code>WHERE num2 = 10000;</code>进行查询：</p><p><img src="https://guide-blog-images.oss-cn-shenzhen.aliyuncs.com/github/javaguide/mysqlindex-invalidation-caused-by-implicit-conversion-03.png" alt="" loading="lazy"></p><p>从结果可以看到，后面插入的三条数据也都匹配上了。那么这个字符串隐式转换的规则是什么呢？为什么<code>num2=&#39;10000a&#39;</code>、<code>&#39;010000&#39;</code>和<code>&#39;10000&#39;</code>这三种情形都能匹配上呢？查阅相关资料发现规则如下：</p><ol><li><strong>不以数字开头</strong>的字符串都将转换为<code>0</code>。如<code>&#39;abc&#39;</code>、<code>&#39;a123bc&#39;</code>、<code>&#39;abc123&#39;</code>都会转化为<code>0</code>；</li><li><strong>以数字开头的</strong>字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止。比如<code>&#39;123abc&#39;</code>会转换为<code>123</code>，<code>&#39;012abc&#39;</code>会转换为<code>012</code>也就是<code>12</code>，<code>&#39;5.3a66b78c&#39;</code>会转换为<code>5.3</code>，其他同理。</li></ol><p>现对以上规则做如下测试验证：</p><p><img src="https://guide-blog-images.oss-cn-shenzhen.aliyuncs.com/github/javaguide/mysqlindex-invalidation-caused-by-implicit-conversion-04.png" alt="" loading="lazy"></p><p>如此也就印证了之前的查询结果了。</p><p>再次写一条 SQL 查询 str1 字段：<code>SELECT * FROM</code>test1<code>WHERE str1 = 1234;</code></p><p><img src="https://guide-blog-images.oss-cn-shenzhen.aliyuncs.com/github/javaguide/mysqlindex-invalidation-caused-by-implicit-conversion-05.png" alt="" loading="lazy"></p><h2 id="分析和总结" tabindex="-1"><a class="header-anchor" href="#分析和总结" aria-hidden="true">#</a> 分析和总结</h2><p>通过上面的测试我们发现 MySQL 使用操作符的一些特性：</p><ol><li>当操作符<strong>左右两边的数据类型不一致</strong>时，会发生<strong>隐式转换</strong>。</li><li>当 where 查询操作符<strong>左边为数值类型</strong>时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。</li><li>当 where 查询操作符<strong>左边为字符类型</strong>时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。</li><li>字符串转换为数值类型时，非数字开头的字符串会转化为<code>0</code>，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。</li></ol><p>所以，我们在写 SQL 时一定要养成良好的习惯，查询的字段是什么类型，等号右边的条件就写成对应的类型。特别当查询的字段是字符串时，等号右边的条件一定要用引号引起来标明这是一个字符串，否则会造成索引失效触发全表扫描。</p><!--]--></div><!----><footer class="page-meta"><div class="meta-item edit-link"><a href="https://github.com/Snailclimb/JavaGuide/edit/main/docs/database/mysql/index-invalidation-caused-by-implicit-conversion.md" rel="noopener noreferrer" target="_blank" arialabel="编辑此页" class="nav-link label"><!--[--><svg xmlns="http://www.w3.org/2000/svg" class="icon edit-icon" viewbox="0 0 1024 1024" arialabelledby="edit"><title id="edit" lang="en">edit icon</title><g fill="currentColor"><path d="M430.818 653.65a60.46 60.46 0 0 1-50.96-93.281l71.69-114.012 7.773-10.365L816.038 80.138A60.46 60.46 0 0 1 859.225 62a60.46 60.46 0 0 1 43.186 18.138l43.186 43.186a60.46 60.46 0 0 1 0 86.373L588.879 565.55l-8.637 8.637-117.466 68.234a60.46 60.46 0 0 1-31.958 11.229z"></path><path d="M728.802 962H252.891A190.883 190.883 0 0 1 62.008 771.98V296.934a190.883 190.883 0 0 1 190.883-192.61h267.754a60.46 60.46 0 0 1 0 120.92H252.891a69.962 69.962 0 0 0-69.098 69.099V771.98a69.962 69.962 0 0 0 69.098 69.098h475.911A69.962 69.962 0 0 0 797.9 771.98V503.363a60.46 60.46 0 1 1 120.922 0V771.98A190.883 190.883 0 0 1 728.802 962z"></path></g></svg><!--]-->编辑此页<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span><!----></a></div><div class="meta-item update-time"><span class="label">上次编辑于: </span><span class="info">2022/4/6 09:52:22</span></div><div class="meta-item contributors"><span class="label">贡献者: </span><!--[--><!--[--><span class="contributor" title="email: koushuangbwcx@163.com">guide</span><!--]--><!--]--></div></footer><nav class="page-nav"><a href="/database/mysql/some-thoughts-on-database-storage-time.html" class="nav-link prev" arialabel="MySQL数据库时间类型数据存储建议"><div class="hint"><span class="arrow left"></span>上一页</div><div class="link"><!---->MySQL数据库时间类型数据存储建议</div></a><!----></nav><!----><!----></main><!--]--><footer class="footer-wrapper"><div class="footer"><a href="https://beian.miit.gov.cn/" target="_blank">鄂ICP备2020015769号-1</a></div><div class="copyright">Copyright © 2022 Guide</div></footer></div><!--]--><!----><!--]--></div>
    <script type="module" src="/assets/app.93341f6d.js" defer></script>
  </body>
</html>
